#!/usr/bin/env python
# coding:utf-8


import logging
from datatabletransfer import DataTableTransfer
from fieldsmap import *


class FundMarketScale(DataTableTransfer):
    """
    基金市场规模表(TQ_FD_FPMTSCALE) --> 基金市场规模(fund_market_scale)
    """
    QUERY_SOURCE = '''select  '基金业协会' as stat_agency,'公募基金' as sec_type,SCALENAME as fund_type,ENDDATE as end_date
    from TQ_FD_FPMTSCALE where COMPNAME='基金市场' and SCALENAME in (1310,1311,1312,131201,131202,131203,131204,131205);'''

    QUERY_TARGET = '''select stat_agency,sec_type,fund_type, replace(end_date,'-','') as end_date
     from fund_market_scale  where  status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select DECLAREDATE,ENDDATE,'基金业协会' COMPNAME,'公募基金' as SEC_TYPE, SCALENAME,MARKETNAV,
    # QUANTITY,SHARES from TQ_FD_FPMTSCALE where COMPNAME='基金市场'
    # and SCALENAME in (1310,1311,1312,131201,131202,131203,131204,131205) limit %s, 1000;''')

    TABLE = 'fund_market_scale'  # 待插入的表名
    SOURCE_ID =  {'stat_agency':False, 'sec_type':False, 'fund_type':True, 'end_date':False}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
    }
    IDX = ('stat_agency', 'sec_type', 'fund_type', 'end_date')

    MAP_FIELDS = FundMarketScaleMap
    MAP_VALUES = FundMarketScaleValueMap


class FundKeeperFund(DataTableTransfer):
    """
    基金管理人管理基金情况(TQ_FD_KEEPERTRFD) --> 基金管理人管理基金情况(fund_keeper_fund)
    """
    QUERY_SOURCE = '''select  KEEPERCODE as fund_keeper_code,ENDDATE as end_date from TQ_FD_KEEPERTRFD;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as fund_keeper_code, replace(end_date,'-','') as end_date
    from fund_keeper_fund f join org_basic_info b on f.fund_keeper_code = b.com_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select ENDDATE,concat('FD',KEEPERCODE) as KEEPERCODE,KEEPERNAME,FDNUMBER,FDSHARE,FDASS,OPFDNUM,
    # OPFDSHA,OPFDASS,CLFDNUM,CLFDSHA,CLFDASS from TQ_FD_KEEPERTRFD limit %s, 1000;''')

    TABLE = 'fund_keeper_fund'  # 待插入的表名
    SOURCE_ID = {'fund_keeper_code':False, 'end_date':False} # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'end_date': None,
    }
    IDX = ('fund_keeper_code', 'end_date')

    MAP_FIELDS = FundKeeperFundMap
    MAP_VALUES = FundKeeperFundValueMap


class FundLeverage(DataTableTransfer):
    """
    基金杠杆表(TQ_FD_LEVERAGE) --> 基金杠杆(fund_leverage)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,TRADEDATE as trade_date  from TQ_FD_LEVERAGE;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.trade_date,'-','') as trade_date
     from fund_leverage f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD', SECURITYID) SECURITYID,TRADEDATE,SHARELEVERAGE,NAVLEVERAGE,PRIXLEVERAGE,
    # BFUNDNAV,BFUNDCLOSE,BFUNDRATIO,MASTERNAV,MASTERRATIO,LOWBASERATIO from TQ_FD_LEVERAGE limit %s, 1000;''')

    TABLE = 'fund_leverage'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'trade_date':False}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'trade_date': None,
    }
    IDX = ('sec_uni_code', 'trade_date')

    MAP_FIELDS = FundLeverageMap
    MAP_VALUES = FundLeverageValueMap


class FundTrustee(DataTableTransfer):
    """
    基金托管人管理基金情况(TQ_FD_TRUSTEETRFD) --> 基金托管人托管基金情况(fund_trustee_fund)
    """
    QUERY_SOURCE = '''select  TRUSTEECODE as fund_trustee_code,ENDDATE as end_date  from TQ_FD_TRUSTEETRFD;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as fund_trustee_code, replace(end_date,'-','') as end_date
    from fund_trustee_fund f join org_basic_info b on f.fund_trustee_code = b.com_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select ENDDATE,concat('FD',TRUSTEECODE) TRUSTEECODE,TRUSTEENAME,FDNUMBER,FDSHARE,FDASS,OPFDNUM,
    # OPFDSHA,OPFDASS,CLFDNUM,CLFDSHA,CLFDASS from TQ_FD_TRUSTEETRFD limit %s, 1000;''')

    TABLE = 'fund_trustee_fund'  # 待插入的表名
    SOURCE_ID = {'fund_trustee_code':False, 'end_date':False}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'end_date': None,
    }
    IDX = ('fund_trustee_code', 'end_date')

    MAP_FIELDS = FundTrusteeMap
    MAP_VALUES = FundTrusteeValueMap


class FundDiscount(DataTableTransfer):
    """
    基金升贴水(TQ_FD_DISCOUNT) --> 基金升贴水(fund_discount)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,TRADEDATE as trade_date  from TQ_FD_DISCOUNT;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.trade_date,'-','') as trade_date
        from fund_discount f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) SECURITYID,TRADEDATE,TCLOSE,ENDDATE,UNITNAV,DISCOUNT,DISCOUNTRATE
    # from TQ_FD_DISCOUNT limit %s, 1000;''')

    TABLE = 'fund_discount'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'trade_date':False}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'trade_date': None,
    }
    IDX = ('sec_uni_code', 'trade_date')

    MAP_FIELDS = FundDiscountMap
    MAP_VALUES = FundDiscountValueMap


class FundMangerStat(DataTableTransfer):
    """
    基金经理统计(TQ_FD_MANAGERSTA) --> 基金经理统计(fund_manager_stat)
    """
    QUERY_SOURCE = '''select  PSCODE as peo_uni_code  from TQ_FD_MANAGERSTA;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as peo_uni_code from fund_manager_stat f 
    join peo_basic_info b on f.peo_uni_code = b.peo_uni_code where  f.status<>'9' and b.come_source='zh-data' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',PSCODE) as PSCODE,PSNAME,TOTCOUNT,KEEPERNAME,INCUMBTBEG,
    # CURFCOUNT from TQ_FD_MANAGERSTA limit %s, 1000;''')

    TABLE = 'fund_manager_stat'  # 待插入的表名
    SOURCE_ID = {'peo_uni_code':False, }  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'present_comp_beg_date': None,
    }
    IDX = ('peo_uni_code', )

    MAP_FIELDS = FundMangerStatMap
    MAP_VALUES = FundMangerStatValueMap


class FundMangerPref(DataTableTransfer):
    """
    基金经理经营业绩表(TQ_FD_MGPERFORMANCE) --> 基金经理经营业绩表(fund_manager_perf)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code, MANAGERCODE as peo_uni_code,BEGINDATE as begin_date 
    from TQ_FD_MGPERFORMANCE;'''

    QUERY_TARGET = '''select replace(s.source_id,'FD','') as sec_uni_code,replace(b.source_id,'FD','') as peo_uni_code,
    replace(begin_date,'-','') as begin_date   from fund_manager_perf f join sec_basic_info s on f.sec_uni_code = s.sec_uni_code 
    join peo_basic_info b on f.peo_uni_code= b.peo_uni_code where  f.status<>'9' and  b.come_source='zh-data';'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,FDSNAME,RANKTYPE,
    # concat('FD',MANAGERCODE) as MANAGERCODE,MANAGERNAME,ISINCUMBENT,BEGINDATE,ENDDATE,
    # NAVRTO,BENCHMARKRTO,TENUREYIELD,TENUREYIELDYR,ALLRANK,ALLAVGYIELD,CLASSRANK,RTYPEAVGYIELD,
    # 1906002 as class_type from TQ_FD_MGPERFORMANCE limit %s, 1000;''')

    TABLE = 'fund_manager_perf'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'peo_uni_code':False, 'begin_date':False}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'begin_date': None,
        'end_date': None,
    }
    IDX = ('sec_uni_code', )
    MAP_FIELDS = FundMangerPrefMap
    MAP_VALUES = FundMangerPrefValueMap


class FundNavReturn(DataTableTransfer):
    """
    基金衍生表(TQ_FD_DERIVEDN) --> 基金净值回报(fund_nav_return)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,ENDDATE as end_date from TQ_FD_DERIVEDN;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.end_date,'-','') as end_date 
     from fund_nav_return f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,ENDDATE,UNITNAV,UNITACCNAV,REPAIRUNITNAV,NAVGRTD,
    # UNITACCNAVGR,CUR,NAVGRL1W,NAVGRL1M,NAVGRL3M,NAVGRL6M,NAVGRL1Y,NAVGRL2Y,NAVGRL3Y,NAVGRL5Y,NAVGRT1W,NAVGRT1M,NAVGRTQ,
    # NAVGRT1Y,NAVGRLIST from TQ_FD_DERIVEDN  limit %s, 1000;''')

    TABLE = 'fund_nav_return'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'end_date': None,
    }
    IDX = ('sec_uni_code', )
    MAP_FIELDS = FundNavReturnMap
    MAP_VALUES = FundNavReturnValueMap


class FundBenchmarkYield(DataTableTransfer):
    """
    基金业绩比较基准日收益率表(TQ_FD_BENCHYIELD) --> 基金业绩基准收益(fund_benchmark_yield)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,BENCHDATE as end_date from TQ_FD_BENCHYIELD where id>0;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.end_date,'-','') as end_date 
        from fund_benchmark_yield f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' and b.sec_uni_code>0 ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID, BENCHDATE,YIELD,INEDXVALUE
    # from TQ_FD_BENCHYIELD where id>0  limit %s, 10000;''')

    TABLE = 'fund_benchmark_yield'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'end_date': None,
    }
    IDX = ('sec_uni_code', 'end_date')
    MAP_FIELDS = FundBenchmarkYieldMap
    MAP_VALUES = FundBenchmarkYieldValueMap

    @classmethod
    def import_mysql_data(cls, table, query, flag):
        """
        导表
        :param table:
        :param query:
        :param flag:
        :return:
        """

        total = cls.get_total_number(flag)
        logging.info('The table {1} total records is {0} items, {2} running...'.format(total, table, flag))
        for index in xrange(0, total, 10000):
            sql = query % index
            rows = cls.export_mysql(sql)
            logging.info('The table {1} records is {0} items, export finished.'.format(index+10000, table))
            cls.upset_mysql(table, rows)
            logging.info('The table {1} records is {0} items, update finished.'.format(index+10000, table))


if __name__ == '__main__':

    log_path = '../logs/export_fund_derivative_server.log'
    logging.basicConfig(filename=log_path,
                        format='%(asctime)s %(name)s %(levelname)s [line %(lineno)s]: %(message)s',
                        filemode='w',
                        level=logging.INFO)

    # FundMarketScale.work()
    # FundKeeperFund.work()
    # FundLeverage.work()
    # FundTrustee.work()
    # FundDiscount.work()
    # FundMangerStat.work()
    # FundMangerPref.work()
    # FundNavReturn.work()
    FundBenchmarkYield.work()
